const { getDbInstance } = require("./database.js");

// 插入新的上传条目
const insertUploadItem = (uploadData, callback) => {
  const stmt = getDbInstance().prepare(`
        INSERT INTO uploads (
          taskid,
          userid,
          status,
          fileName,
          fileSize,
          filePath,
          fileOwner,
          fileRemotePath,
          fileMimeType,
          fileType,
          progress,
          uploadDate,
          completedDate,
          groupDeviceId,
          syKeyDid,
          syKeyInitString,
          syKeyApiLicenses,
          syKeyCrcKey,
          playload,
          code
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `);

  stmt.run(
    [
      uploadData.taskid,
      uploadData.userid,
      uploadData.status,
      uploadData.fileName,
      uploadData.fileSize,
      uploadData.filePath,
      uploadData.fileOwner,
      uploadData.fileRemotePath,
      uploadData.fileMimeType,
      uploadData.fileType,
      uploadData.progress,
      uploadData.uploadDate,
      uploadData.completedDate,
      uploadData.groupDeviceId,
      uploadData.syKeyDid,
      uploadData.syKeyInitString,
      uploadData.syKeyApiLicenses,
      uploadData.syKeyCrcKey,
      JSON.stringify(uploadData.playload),
      uploadData.code,
    ],
    callback
  );

  stmt.finalize();
};

// 插入新的下载条目 - 批量
const insertUploadItems = (uploadDataArray, callback) => {
  getDbInstance().serialize(() => {
    getDbInstance().run("BEGIN TRANSACTION"); // 开始事务

    const stmt = getDbInstance().prepare(`
        INSERT INTO uploads (
          taskid,
          userid,
          status,
          fileName,
          fileSize,
          filePath,
          fileOwner,
          fileRemotePath,
          fileMimeType,
          fileType,
          progress,
          uploadDate,
          completedDate,
          groupDeviceId,
          syKeyDid,
          syKeyInitString,
          syKeyApiLicenses,
          syKeyCrcKey,
          playload,
          code
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `);

    uploadDataArray.forEach((uploadData) => {
      stmt.run(
        uploadData.taskid,
        uploadData.userid,
        uploadData.status,
        uploadData.fileName,
        uploadData.fileSize,
        uploadData.filePath,
        uploadData.fileOwner,
        uploadData.fileRemotePath,
        uploadData.fileMimeType,
        uploadData.fileType,
        uploadData.progress,
        uploadData.uploadDate,
        uploadData.completedDate,
        uploadData.groupDeviceId,
        uploadData.syKeyDid,
        uploadData.syKeyInitString,
        uploadData.syKeyApiLicenses,
        uploadData.syKeyCrcKey,
        JSON.stringify(uploadData.playload),
        uploadData.code
      );
    });

    stmt.finalize();
    getDbInstance().run("COMMIT", callback); // 提交事务并调用回调函数
  });
};

// 获取指定用户和groupDeviceId的所有上传条目
const getUserUploadsByDeviceId = (
  {
    userid,
    groupDeviceId,
    statusArr,
    orderBy = "taskid",
    orderDirection = "ASC",
    page = 1,
    pageSize = 1000,
  } = {},
  callback
) => {
  getDbInstance().serialize(() => {
    let sql = "SELECT * FROM uploads WHERE userid = ? AND groupDeviceId = ?";
    let countSql =
      "SELECT COUNT(*) as total FROM uploads WHERE userid = ? AND groupDeviceId = ?";
    let params = [userid, groupDeviceId];

    // console.log('[getUserUploadsByDeviceId statusArr]', statusArr);

    if (statusArr && statusArr.length > 0) {
      // Prepare the placeholder for each status
      const placeholders = statusArr.map(() => "?").join(",");
      sql += ` AND status IN (${placeholders})`;
      countSql += ` AND status IN (${placeholders})`;
      params = [...params, ...statusArr];
    }

    const countParams = [...params];

    // Add order by and direction
    sql += ` ORDER BY ${orderBy} ${orderDirection}`;

    // Add pagination
    const offset = (page - 1) * pageSize;
    sql += " LIMIT ? OFFSET ?";
    params.push(pageSize, offset);

    getDbInstance().get(
      countSql,
      countParams,
      (countErr, { total = 0 } = {}) => {
        if (countErr) {
          console.error("Failed to count user uploads:", countErr);
          callback({ total, data: [], page, pageSize });
          return;
        }

        getDbInstance().all(sql, params, (err, rows) => {
          if (err) {
            console.error("Failed to fetch user uploads:", err);
            callback({ total, data: [], page, pageSize });
          } else {
            const uploads = rows.map((row) => {
              row.playload = JSON.parse(row.playload);
              return row;
            });

            callback({ total, data: uploads, page, pageSize });
          }
        });
      }
    );
  });
};

// 获取数据库中所有状态和其对应的数量
const getUploadStatusCount = ({ userid, groupDeviceId }) => {
  return new Promise((resolve, reject) => {
    getDbInstance().serialize(() => {
      getDbInstance().all(
        "SELECT status, COUNT(*) as count FROM uploads WHERE userid = ? AND groupDeviceId = ? GROUP BY status",
        [userid, groupDeviceId],
        (err, rows) => {
          if (err) {
            console.error(err);
            reject(err);
          } else {
            const statusCount = {};
            rows.forEach((row) => {
              statusCount[row.status] = row.count;
            });
            resolve(statusCount);
          }
        }
      );
    });
  });
};

// 更新上传条目的状态
const updateUploadStatus = (
  { taskid, status, completedDate, progress, code },
  callback
) => {
  getDbInstance().serialize(() => {
    getDbInstance().run(
      "UPDATE uploads SET status = ?, completedDate = ?, progress = ?, code = ? WHERE taskid = ?",
      [status, completedDate, progress, code, taskid],
      callback
    );
  });
};

const updateAllUploadStatus = ({
  userid,
  groupDeviceId,
  oldStatuses = [],
  newStatus,
  code = 3,
  taskids,
}) => {
  return new Promise((resolve, reject) => {
    getDbInstance().serialize(() => {
      const placeholders = oldStatuses.map(() => "?").join(",");

      // First, get the IDs of all the rows we want to update
      let selectSql = `
      SELECT taskid
      FROM uploads 
      WHERE status IN (${placeholders})
      AND userid = ?
      AND groupDeviceId = ?`;

      let params = [...oldStatuses, userid, groupDeviceId];

      // Modify SQL and parameters if taskids is provided
      if (Array.isArray(taskids) && taskids.length > 0) {
        const taskidPlaceholders = taskids.map(() => "?").join(",");
        selectSql += ` AND taskid IN (${taskidPlaceholders})`;
        params = params.concat(taskids);
      }

      getDbInstance().all(selectSql, params, (error, rows) => {
        if (error) {
          reject(error);
        } else {
          const idsToUpdate = rows.map((row) => row.taskid);

          const updateBatch = () => {
            if (idsToUpdate.length === 0) {
              resolve();
              return;
            }

            // Take the next 1000 IDs
            const batchIds = idsToUpdate.splice(
              0,
              Math.min(1000, idsToUpdate.length)
            );
            const idPlaceholders = batchIds.map(() => "?").join(",");

            const updateSql = `
            UPDATE uploads
            SET status = ?, code = ?
            WHERE taskid IN (${idPlaceholders})`;

            const params = [newStatus, code, ...batchIds];

            getDbInstance().run(updateSql, params, (error) => {
              if (error) {
                reject(error);
              } else {
                updateBatch(); // If there are more rows to update, do another batch
              }
            });
          };

          updateBatch(); // Start the first batch
        }
      });
    });
  });
};

const deleteAllUploadStatus = ({
  userid,
  groupDeviceId,
  statusesToDelete = [],
}) => {
  return new Promise((resolve, reject) => {
    getDbInstance().serialize(() => {
      const placeholders = statusesToDelete.map(() => "?").join(",");

      const deleteSql = `
      DELETE FROM uploads 
      WHERE status IN (${placeholders})
      AND userid = ?
      AND groupDeviceId = ?`;

      getDbInstance().run(
        deleteSql,
        [...statusesToDelete, userid, groupDeviceId],
        (error) => {
          if (error) {
            reject(error);
          } else {
            resolve();
          }
        }
      );
    });
  });
};

// 根据一个或多个taskid删除上传条目
const deleteUploadByTaskIds = (taskids, callback) => {
  if (!Array.isArray(taskids) || taskids.length === 0) {
    callback(new Error("Invalid taskids provided. Must be a non-empty array."));
    return;
  }

  const placeholders = taskids.map(() => "?").join(",");
  const sql = `DELETE FROM uploads WHERE taskid IN (${placeholders})`;

  getDbInstance().run(sql, taskids, (err) => {
    if (err) {
      console.error("Failed to delete upload(s) by taskid(s):", err);
      callback(err);
    } else {
      callback(null);
    }
  });
};

// 批量更新上传条目的状态
const updateUploadStatusBulk = (updates, callback) => {
  getDbInstance().serialize(() => {
    getDbInstance().run("BEGIN TRANSACTION");
    updates.forEach(({ taskid, status, completedDate, progress, code }) => {
      getDbInstance().run(
        "UPDATE uploads SET status = ?, completedDate = ?, progress = ?, code = ? WHERE taskid = ?",
        [status, completedDate, progress, code, taskid],
        (err) => {
          if (err) {
            getDbInstance().run("ROLLBACK");
            callback(err);
          }
        }
      );
    });
    getDbInstance().run("COMMIT", callback);
  });
};

const getUploadByTaskid = (taskid) => {
  return new Promise((resolve, reject) => {
    const sql = "SELECT * FROM uploads WHERE taskid = ?";

    getDbInstance().get(sql, [taskid], (err, row) => {
      if (err) {
        reject(err);
      } else {
        if (row && row.playload) {
          row.playload = JSON.parse(row.playload);
        }
        resolve(row);
      }
    });
  });
};
module.exports = {
  insertUploadItem,
  insertUploadItems,
  getUserUploadsByDeviceId,
  getUploadStatusCount,
  updateUploadStatus,
  deleteUploadByTaskIds,
  updateUploadStatusBulk,
  updateAllUploadStatus,
  deleteAllUploadStatus,
  getUploadByTaskid,
};
